package com.peiwan.ranking.repository;

import com.peiwan.ranking.dto.OrderDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.Date;
import java.util.List;

@Repository
public interface OrderRepository extends JpaRepository<OrderDTO, Long> {
    
    @Query("SELECT o FROM OrderDTO o WHERE DATE(o.createTime) = DATE(:date)")
    List<OrderDTO> findByDate(Date date);
    
    default List<OrderDTO> findByYearAndMonth(int year, int month) {
        try {
            LocalDate startDate = LocalDate.of(year, month, 1);
            LocalDate endDate = startDate.withDayOfMonth(startDate.lengthOfMonth());
            return findByDateBetween(
                Date.from(startDate.atStartOfDay(ZoneId.systemDefault()).toInstant()),
                Date.from(endDate.atTime(23, 59, 59).atZone(ZoneId.systemDefault()).toInstant())
            );
        } catch (Exception e) {
            throw new RuntimeException("查询订单数据失败: " + e.getMessage(), e);
        }
    }
    
    @Query("SELECT new com.peiwan.ranking.dto.OrderDTO(o.id, o.createTime, o.companion, o.dispatch, o.receptionist, o.totalPrice, o.finalAmount, o.groupCommission, o.dispatchCommission, o.receptionCommission) FROM OrderDTO o WHERE o.createTime BETWEEN :startDate AND :endDate")
    List<OrderDTO> findByDateBetween(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
    
    @Query("SELECT o.companion as name, SUM(o.totalPrice) as amount, SUM(o.groupCommission) as groupCommission " +
           "FROM OrderDTO o WHERE YEAR(o.createTime) = :year AND MONTH(o.createTime) = :month " +
           "GROUP BY o.companion ORDER BY amount DESC")
    List<Object[]> getMonthlyRanking(int year, int month);
}